from openpyxl import load_workbook
import os
import numpy as np

folder_path = 'd:/data/'
for root, dirs, files in os.walk(folder_path):
    for file1 in files:
        file_path = os.path.join(root, file1)
        print(file_path)
        if '.xlsx' in file_path:
            wb = load_workbook(file_path)
            ws = wb.active
            for row in range(2, ws.max_row + 1):
                cell_values = [ws.cell(row, col).value for col in range(3, 6)]
                if cell_values[0] is not None and cell_values[1] is not None and cell_values[2] is None:
                    for i in range(5, ws.max_column + 1):
                        cell_above = ws.cell(row - 1, i).value
                        if cell_above is not None:
                            data = [ws.cell(r, i).value for r in range(row - 33, row - 1) if ws.cell(r, i).value is not None]
                            if data:
                                mean = sum(data) / len(data)
                                variance = sum((x - mean) ** 2 for x in data) / len(data)
                                std_dev = variance ** 0.5
                                random_value = np.random.normal(mean, std_dev)
                                ws.cell(row, i).value = round(random_value, 3)
            wb.save(file_path)
            print("保存成功")